package com.bluedot.www.core.utils;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.bluedot.www.core.common.excel.AutoColumnWidthStrategy;
import com.bluedot.www.core.common.excel.CustomerCellStyleStrategy;
import com.bluedot.www.core.common.excel.ExcelListener;
import com.bluedot.www.core.pojo.BO.ExcelCustomer;
import com.bluedot.www.core.pojo.BO.ExcelFamilyNumber;
import com.bluedot.www.core.pojo.BO.ExcelLog;
import com.bluedot.www.core.pojo.DO.Customer;
import com.bluedot.www.core.pojo.DO.FamilyNumber;
import com.bluedot.www.core.pojo.DO.Log;
import com.bluedot.www.core.pojo.DO.Student;
import com.bluedot.www.core.pojo.DTO.CustomerDTO;
import com.bluedot.www.core.pojo.DTO.ExcelCustomerDTO;
import com.bluedot.www.framework.mvc.utils.Assert;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @author Ding YuXing
 * @version 1.0
 * @date 2021/9/12 10:46
 */
public class ExcelUtils {

    /**
     * 复制字节时，默认的缓冲区大小
     */
    private static final int BUFFER_SIZE = 1024;


    public static <T> XSSFWorkbook getWorkbook(Collection<T> dataSet, String[] params, String[] titles) {
        // 校验变量和预期输出excel列数是否相同
        if (params.length != titles.length) {
            System.out.println("变量参数有误");
            return null;
        }
        // 存储每一行的数据
        List<String[]> list = new ArrayList<>();
        for (Object obj : dataSet) {
            // 获取到每一行的属性值数组
            list.add(getValues(obj, params));
        }
        return getWorkbook(titles, list);
    }

    public static XSSFWorkbook getWorkbook(String[] titles, List<String[]> list) {
        // 定义表头
        String[] title = titles;
        // 创建excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表sheet
        XSSFSheet sheet = workbook.createSheet();
        // 创建第一行
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;
        // 插入第一行数据的表头
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        int idx = 1;
        for (String[] strings : list) {
            XSSFRow nrow = sheet.createRow(idx++);
            XSSFCell ncell = null;
            for (int i = 0; i < strings.length; i++) {
                ncell = nrow.createCell(i);
                ncell.setCellValue(strings[i]);
            }
        }
        // 设置自动列宽
        for (int i = 0; i < title.length; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
        }
        return workbook;
    }

    // 根据需要输出的变量名数组获取属性值
    public static String[] getValues(Object object, String[] params) {
        String[] values = new String[params.length];
        try {
            for (int i = 0; i < params.length; i++) {
                Field field = object.getClass().getDeclaredField(params[i]);
                // 设置访问权限为true
                field.setAccessible(true);
                // 获取属性
                // 如果属性有涉及基本变量的做一个转换
                if (field.getType() == com.sun.org.apache.bcel.internal.generic.Type.INT.getClass()) {
                    values[i] = String.valueOf((int) field.get(object));
                }
                values[i] = field.get(object).toString();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return values;
    }


//    =======================================================================


    /**
     * 将pojo的log对象转化为Excel
     *
     * @param list List<Log>实体类数据
     * @return InputStream
     * @author Deng HongWei
     * @date 2021/8/8 16:37
     */
    public static InputStream logToExcel(List<Log> list, ByteArrayOutputStream out) {
        ArrayList<ExcelLog> excelLogs = new ArrayList<>();
        for (Log log : list) {
            excelLogs.add(new ExcelLog(log));
        }

        //调用easyExcel里面的方法实现写操作
        WriteSheet writeSheet0 = EasyExcel.writerSheet(0, "日志信息").head(ExcelLog.class).build();
        // 如果是浏览器下载, 需要设置不关闭流
        ExcelWriter excelWriter = EasyExcel.write(out)
                .autoCloseStream(Boolean.FALSE)
                .excelType(ExcelTypeEnum.XLSX)
                .registerWriteHandler(new CustomerCellStyleStrategy())
                .registerWriteHandler(new AutoColumnWidthStrategy())
                .build();
        //写数据
        excelWriter.write(excelLogs, writeSheet0);
        excelWriter.finish();
        InputStream in = new ByteArrayInputStream(out.toByteArray());
        closeOutStream(out);
        return in;
    }
    /**
     * 将流（Excel）转化为实体类（客户）
     *
     * @param in 前端传进来的输入流
     * @return com.bluedot.www.core.pojo.DTO.CustomerDTO
     * @author Deng HongWei
     * @date 2021/8/8 16:36
     */
    public static List<CustomerDTO> excelToCustomerDTO(InputStream in) {
        // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
        ExcelListener<ExcelCustomer> listener1 = new ExcelListener<>(new ArrayList<>(20));
        ExcelListener<ExcelFamilyNumber> listener2 = new ExcelListener<>(new ArrayList<>(60));
        //读取不同的Sheet，所以上传客户信息时要按照一定格式导入
        ReadSheet readSheet1 = EasyExcel.readSheet(0).head(ExcelCustomer.class).registerReadListener(listener1).build();
        ReadSheet readSheet2 = EasyExcel.readSheet(1).head(ExcelFamilyNumber.class).registerReadListener(listener2).build();
        //读取输入流，读数据
        ExcelReader excelReader = EasyExcel.read(in).autoCloseStream(Boolean.FALSE).build();
        excelReader.read(readSheet1, readSheet2);
        excelReader.finish();
        //获取读到的数据，并将他们封装成相应customerDTO列表数据，并返回给service调用
        List<ExcelCustomer> list1 = listener1.getList();
        List<ExcelFamilyNumber> list2 = listener2.getList();
        List<CustomerDTO> customerDTOS = parseIntoCustomerDTO(list1, list2);
        Assert.notNull(customerDTOS, "excel表内无数据");
        closeInStream(in);
        return customerDTOS;
    }

    /**
     * 将pojo对象转化为Excel
     *
     * @param list List<CustomerDTO>实体类数据
     * @return InputStream
     * @author Deng HongWei
     * @date 2021/8/8 16:37
     */
    public static InputStream customerDTOToExcel(List<CustomerDTO> list, ByteArrayOutputStream out) {
        List<ExcelCustomerDTO> excelData = parseIntoExcelCustomerDTO(list);
        ArrayList<ExcelCustomer> excelCustomers = new ArrayList<>();
        ArrayList<ExcelFamilyNumber> excelFamilyNumbers = new ArrayList<>();
        for (ExcelCustomerDTO excelDatum : excelData) {
            excelCustomers.add(excelDatum.getExcelCustomer());
            excelFamilyNumbers.addAll(excelDatum.getExcelFamilyNumber());
        }


        //调用easyExcel里面的方法实现写操作
        WriteSheet writeSheet0 = EasyExcel.writerSheet(0, "客户信息").head(ExcelCustomer.class).build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "家庭成员信息").head(ExcelFamilyNumber.class).build();
        // 如果是浏览器下载, 需要设置不关闭流
        ExcelWriter excelWriter = EasyExcel.write(out)
                .autoCloseStream(Boolean.FALSE)
                .excelType(ExcelTypeEnum.XLSX)
                .registerWriteHandler(new CustomerCellStyleStrategy())
                .registerWriteHandler(new AutoColumnWidthStrategy())
                .build();
        //写数据
        excelWriter.write(excelCustomers, writeSheet0);
        excelWriter.write(excelFamilyNumbers, writeSheet1);
        excelWriter.finish();
        InputStream in = new ByteArrayInputStream(out.toByteArray());
        closeOutStream(out);
        return in;
    }

    /**
     * 将输入流包装成zip文件
     *
     * @param response
     * @return boolean
     * @author Deng HongWei
     * @date 2021/9/9 19:21
     */
    public static boolean exportToZip(List<String> urls, String zipName, List<String> names, HttpServletResponse response) {
        BufferedOutputStream bos = null;
        ZipOutputStream zos = null;
        try {
            bos = new BufferedOutputStream(response.getOutputStream());
            zos = new ZipOutputStream(bos);
            zos.setEncoding("UTF-8");
            for (int i = 0; i < urls.size(); i++) {
                InputStream in = OSSManageUtil.getFileInputStream(urls.get(i));
                BufferedInputStream bis = new BufferedInputStream(in);
                //文件.docx
                String[] split = names.get(i).split(".");
                String fileName = URLEncoder.encode(split[0] + "-" + (i + 1) + split[1], "UTF-8");
                zos.putNextEntry(new ZipEntry(fileName));
                byte[] bytes = new byte[BUFFER_SIZE];
                int len;
                while ((len = bis.read(bytes)) != -1) {
                    zos.write(bytes, 0, len);
                }
                zos.closeEntry();
                zos.flush();
                closeInStream(in);
                closeInStream(bis);
            }
            //设置返回文件类型与字符集
            String fileName = URLEncoder.encode(zipName, "UTF-8");
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".zip");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //关闭流
            closeOutStream(bos);
            closeOutStream(zos);
        }
        return true;
    }

    /**
     * 用于关闭输入流（不用try）
     *
     * @param in 输入流超类
     * @return void
     * @author Deng HongWei
     * @date 2021/9/9 19:14
     */
    public static void closeInStream(InputStream in) {
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 用于关闭输出流（不用try）
     *
     * @param out 输出流超类
     * @return void
     * @author Deng HongWei
     * @date 2021/9/9 19:15
     */
    public static void closeOutStream(OutputStream out) {
        if (out != null) {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * CustomerDTO实体类列表转ExcelCustomerDTO列表
     *
     * @param list CustomerDTO实体类列表
     * @return java.util.List<com.bluedot.www.core.common.excel.pojo.ExcelCustomerDTO>
     * @author Deng HongWei
     * @date 2021/9/9 14:35
     */
    private static List<ExcelCustomerDTO> parseIntoExcelCustomerDTO(List<CustomerDTO> list) {
        List<ExcelCustomerDTO> excelList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Customer customer = list.get(i).getCustomer();
            ExcelCustomerDTO excelCustomerDTO = new ExcelCustomerDTO();
            String genderStr = customer.getGender() == 0 ? "男" : "女";
            Integer sort = customer.getSort();
            String sortStr = sort > 5 ? (sort > 10 ? "重要" : "中等") : "一般";
            String stausStr = customer.getStatus() == 0 ? "是" : "否";
            excelCustomerDTO.setExcelCustomer(new ExcelCustomer(i + 1, customer.getName(), genderStr, customer.getBirthday(),
                    customer.getProfessional(), customer.getPhoneNum(), customer.getInterest(), customer.getDescription(), customer.getNativePlace(),
                    customer.getAddress(), customer.getMedicalHistory(), sortStr));

            List<FamilyNumber> familyNumbers = list.get(i).getFamilyNumbers();
            List<ExcelFamilyNumber> excelFamilyNumbers = new ArrayList<>();
            for (int j = 0; j < familyNumbers.size(); j++) {
                FamilyNumber familyNumber = familyNumbers.get(j);
                String genderStrs = familyNumber.getGender() == 0 ? "男" : "女";
                ExcelFamilyNumber excelFamilyNumber = new ExcelFamilyNumber(i + 1, familyNumber.getName(), genderStrs, familyNumber.getRelationship(), familyNumber.getPhoneNum());
                excelFamilyNumbers.add(excelFamilyNumber);
            }
            excelCustomerDTO.setExcelFamilyNumber(excelFamilyNumbers);
            excelList.add(excelCustomerDTO);
        }
        return excelList;
    }

    /**
     * 将excel表格读取出的ExcelCustomer列表、ExcelCustomer列表组装成List<CustomerDTO>列表
     *
     * @param list1 ExcelCustomer列表
     * @param list2 ExcelCustomer列表
     * @return java.util.List<com.bluedot.www.core.pojo.DTO.CustomerDTO>
     * @author Deng HongWei
     * @date 2021/9/9 14:36
     */
    private static List<CustomerDTO> parseIntoCustomerDTO(List<ExcelCustomer> list1, List<ExcelFamilyNumber> list2) {
        List<CustomerDTO> customerDTOS = new ArrayList<>();
        for (ExcelCustomer excelCustomer : list1) {
            Integer id = excelCustomer.getId();
            Assert.notNull(id, "导入格式错误，客户信息缺少序号");
            CustomerDTO customerDTO = new CustomerDTO();
            Customer customer = new Customer();
            customer.setName(excelCustomer.getName());
            customer.setGender("男".equals(excelCustomer.getGender()) ? 0 : ("女".equals(excelCustomer.getGender())) ? 1 : 0);
            customer.setBirthday(excelCustomer.getBirthday());
            customer.setProfessional(excelCustomer.getProfessional());
            customer.setPhoneNum(excelCustomer.getPhoneNum());
            customer.setInterest(excelCustomer.getInterest());
            customer.setDescription(excelCustomer.getDescription());
            customer.setNativePlace(excelCustomer.getNativePlace());
            customer.setAddress(excelCustomer.getAddress());
            customer.setMedicalHistory(excelCustomer.getMedicalHistory());
            String sort = excelCustomer.getSort();
            Integer integerSort = "重要".equals(sort) ? 15 : ("一般".equals(sort) ? 10 : 5);
            customer.setSort(integerSort);
            customerDTO.setCustomer(customer);
            ArrayList<FamilyNumber> numbers = new ArrayList<>();
            for (ExcelFamilyNumber excelFamilyNumber : list2) {
                Integer numberId = excelFamilyNumber.getCustomerId();
                Assert.notNull(numberId, "导入格式错误，家庭成员信息缺少客户序号");
                if (id.equals(numberId)) {
                    FamilyNumber familyNumber = new FamilyNumber();
                    familyNumber.setName(excelFamilyNumber.getName());
                    familyNumber.setGender("男".equals(excelFamilyNumber.getGender()) ? 0 : ("女".equals(excelFamilyNumber.getGender())) ? 1 : null);
                    familyNumber.setRelationship(excelFamilyNumber.getRelationship());
                    familyNumber.setPhoneNum(excelFamilyNumber.getPhoneNum());
                    numbers.add(familyNumber);
                }
            }
            customerDTO.setFamilyNumbers(numbers);
            customerDTOS.add(customerDTO);
        }
        return customerDTOS;
    }


    public static void main(String[] args) {
        List<Student> list = new ArrayList<>();
        list.add(new Student(1, "张三", "15", 15));
        list.add(new Student(1, "张三", "15", 15));
        list.add(new Student(1, "张三", "15", 15));
        list.add(new Student(1, "张三", "15", 15));

        XSSFWorkbook workbook = getWorkbook(list, new String[]{"id", "name", "email", "age"}, new String[]{"id", "姓名", "邮箱", "年龄"});

        if (workbook != null) {
            try {
                //xlsx1.xlsx
                OutputStream out = new FileOutputStream("D:\\IDEAstart\\MyFrame\\iSpringMVC\\src\\main\\resources\\testXlsx\\xlsx1.xlsx");
                workbook.write(out);
                System.out.println("导出完成");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}
